package com.lm.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import com.lm.dao.SubProductDAO;
import com.lm.model.Subproduct;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.util.DBUtil;

public class SubProductDAOImpl implements SubProductDAO{
	private ComboPooledDataSource dataSource;

	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	public void addSubProductByUserID(Subproduct subProduct) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();
		sb.append(" insert into lm_subproduct(typeID,attribute,proDetailinfo,");
		sb.append(" techParam,productID)");
		sb.append(" values('").append(subProduct.getTypeId()).append("',");
		sb.append("'").append(subProduct.getAttribute()).append("',");
		sb.append("'").append(subProduct.getProDetailinfo()).append("',");
		sb.append("'").append(subProduct.getTechParam()).append("',");
		sb.append("'").append(subProduct.getProductId()).append("')");

		try {
			con = dataSource.getConnection();
			con.setAutoCommit(false);
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate();

			con.commit();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	public void editSubProductByProductID(Subproduct subProduct)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append("update lm_subproduct set typeID=").append(
				subProduct.getTypeId());
		sb.append(",attribute='").append(subProduct.getAttribute());
		sb.append("',proDetailinfo='").append(subProduct.getProDetailinfo());
		sb.append("',techParam='").append(subProduct.getTechParam())
				.append("'");
		sb.append(" where productID=").append(subProduct.getProductId());
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	// public void editSubProductByProductID(Subproduct form)
	// throws Throwable {
	// Connection con = null;
	// PreparedStatement pStmt = null;
	// ResultSet rs = null;
	// StringBuilder sb = new StringBuilder();
	//
	// sb.append("update lm_subproduct set attribute='").append(
	// form.getAttribute());
	// sb.append("',proDetailinfo='").append(form.getProductDatailInfo());
	// sb.append("',techParam='").append(form.getTechParam()).append("'");
	// sb.append(" where productID=").append(form.getProductID());
	// try {
	// con = dataSource.getConnection();
	//
	// pStmt = con.prepareStatement(sb.toString());
	// pStmt.executeUpdate(sb.toString());
	// } catch (Exception e) {
	// e.printStackTrace();
	// }
	// }

	public void deleteSubProductByProductID(int productID) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		StringBuilder sb = new StringBuilder();
		sb.append(" delete from lm_subproduct ");
		sb.append(" where productID=").append(productID);
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	public void deleteSubProductByTypeID(int typeID) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		StringBuilder sb = new StringBuilder();
		sb.append(" delete from lm_subproduct ");
		sb.append(" where typeID=").append(typeID);
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	public void deleteSubProductByAllProductID(String productIDs)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		StringBuilder sb = new StringBuilder();
		sb.append(" delete from lm_subproduct ");
		sb.append(" where productID in(").append(productIDs).append(")");
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	public Subproduct getSubProductIDByProductID(int productID)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		Subproduct sp = new Subproduct();

		StringBuilder sb = new StringBuilder();

		sb.append(" select * from lm_subproduct  ");
		sb.append(" where productID= ").append(productID);
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				sp.setAttribute(rs.getString("attribute"));
				sp.setId(rs.getInt("id"));
				sp.setProDetailinfo(rs.getString("proDetailinfo"));
				sp.setProductId(rs.getInt("productID"));
				sp.setTechParam(rs.getString("techParam"));
				sp.setTypeId(rs.getInt("typeID"));
			}
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return sp;
	}
}
